package com.xh.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONArray;
import com.xh.common.JDBCUtils;
import com.xh.model.ProcedureTable;

import oracle.jdbc.OracleTypes;
@Repository
@Service("iProcedureDao")
public class IProcedureDaoImpl implements IProcedureDao {

	@Override
	public JSONArray getValueByProcedure(ProcedureTable pTable) {
		Connection conn = null;
		PreparedStatement ps=null;
		CallableStatement cstate = null;	
		JSONArray jsonArray=new JSONArray();
		try {
			conn = JDBCUtils.getConnection();			
			String parameters = "(";
			for (int i = 0; i <pTable.getParametersNumber() ; i++) {
				parameters=parameters+"?,";
			}
			String str=parameters.substring(0,parameters.lastIndexOf(",")).concat(")");				
			String sql="{call "+pTable.getProcedureName()+str+"}";
			System.out.println(sql);
			
			cstate=conn.prepareCall(sql);			
			String[] type= pTable.getParametersType().split(",");	
			for (int i = 1; i <= pTable.getParametersNumber(); i++) {	
				switch (type[i-1]){
				case "String":						
					cstate.setString(i,pTable.getParameters().get(i-1).toString());
					break;
				case "Double":					
					cstate.setDouble(i,Double.parseDouble(pTable.getParameters().get(i-1).toString()));					
					break;
				case "int":					
					cstate.setInt(i,Integer.parseInt(pTable.getParameters().get(i-1).toString()));					
					break;
				case "Integer":					
					cstate.setInt(i,Integer.parseInt(pTable.getParameters().get(i-1).toString()));					
					break;
				case "Types.INTEGER":					
					cstate.registerOutParameter(i,Types.INTEGER);					
					break;
				case "OracleTypes.CURSOR":					
					cstate.registerOutParameter(i,OracleTypes.CURSOR);					
					break;				
				}				
			}			
			cstate.execute();
			
			jsonArray.add(cstate);
			System.out.println("ok!");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.free(null, ps, conn);
		}
		return jsonArray;
	}

	@Override
	public Map<String, ProcedureTable> findAllProcedureTable() {
		Connection conn = null;
		PreparedStatement ps=null;
		Statement sta = null ;  
		ResultSet rs=null;
		Map<String, ProcedureTable> ProcedureTableMap=new  HashMap<String, ProcedureTable>();
		ProcedureTable pt=new ProcedureTable();
		try{			
			conn = JDBCUtils.getConnection();
			String sql="select code,procedureName,parametersNumber,parametersType,resultType from proceduretable";
			sta =conn.createStatement();
			 rs=sta.executeQuery(sql);			
		    while (rs.next()) {		    	
		    	pt.setCode(rs.getString("code"));
		    	pt.setProcedureName(rs.getString("procedureName"));
		    	pt.setParametersNumber(rs.getInt("parametersNumber"));
				pt.setParametersType(rs.getString("parametersType"));
				pt.setResultType(rs.getString("resultType"));
				ProcedureTableMap.put(rs.getString("code"), pt);
			}			 
		}catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				sta.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}			
			JDBCUtils.free(null, ps, conn);		
		}		
		return ProcedureTableMap;
	}
}
